package com.qjdchina.sku.util;

import java.io.InputStream;
import java.math.BigDecimal;
import java.util.*;

import javax.servlet.http.HttpSession;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFClientAnchor;
import org.apache.poi.hssf.usermodel.HSSFPicture;
import org.apache.poi.hssf.usermodel.HSSFPictureData;
import org.apache.poi.hssf.usermodel.HSSFShape;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.PictureData;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFClientAnchor;
import org.apache.poi.xssf.usermodel.XSSFDrawing;
import org.apache.poi.xssf.usermodel.XSSFPicture;
import org.apache.poi.xssf.usermodel.XSSFShape;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.multipart.MultipartFile;

import com.qjdchina.commons.result.Result;
import com.qjdchina.sku.enums.IsCustomizable;
import com.qjdchina.sku.model.vo.SkuItemAttributeVO;
import com.qjdchina.sku.model.vo.SkuProductVO;
import com.qjdchina.sku.service.enums.SkuResultCode;

/**
 * 读取excel数据
 * 
 * @author shenpingfeng 2016年4月15日
 */
public class ReadExcelUtil {

    private static final Logger LOGGER = LoggerFactory.getLogger(ReadExcelUtil.class);
    
    private static final Integer FIX_TITLE = 8;

    private static boolean isExcel2003(String filePath) {

        return filePath.matches("^.+\\.(?i)(xls)$");

    }

    private static boolean isExcel2007(String filePath) {

        return filePath.matches("^.+\\.(?i)(xlsx)$");

    }
    
    @SuppressWarnings("resource")
    public static Result<List<SkuProductVO>> createProduct(MultipartFile multipartFile, List<String> skuNames, HttpSession
            session) {
        Workbook wookbook = null;
        String fileName = multipartFile.getOriginalFilename();
        boolean excel2003 = isExcel2003(fileName);
        boolean excel2007 = isExcel2007(fileName);
        List<SkuProductVO> skuProductVOs = new ArrayList<>();
        Map<Integer, String> skuLocation = new HashMap<Integer, String>();


        try {
            InputStream is = multipartFile.getInputStream();
            // 创建对Excel工作簿文件的引用
            if (excel2003) {
                wookbook = new HSSFWorkbook(is);
            } else if (excel2007) {
                wookbook = new XSSFWorkbook(is);
            } else {
                return new Result<List<SkuProductVO>>(SkuResultCode.DATA_ERROR);
            }
            // 关闭流
            is.close();

            // 在Excel文档中，第一张工作表的缺省索引是0
            Sheet sheet = wookbook.getSheetAt(0);
            // 获取到Excel文件中的所有行数
            int rows = sheet.getPhysicalNumberOfRows();
            int count = 0;

            Map<String, List<String>> picIndexMap = new LinkedHashMap<>();
            Map<String, PictureData> sheetIndexPicMap = new LinkedHashMap<>();
            if (excel2003) {
                sheetIndexPicMap = getSheetPictrues03(picIndexMap, (HSSFSheet) sheet, (HSSFWorkbook) wookbook);
            }
            if (excel2007) {
                sheetIndexPicMap = getSheetPictrues07(picIndexMap, (XSSFSheet) sheet, (XSSFWorkbook) wookbook);
            }

            //判断固定表头是否有详情
            Boolean isHasDetail = false;
            // 遍历行
            for (int i = 0; i < rows; i++) {
                Map<String, PictureData> sheetPictrues = new LinkedHashMap<>();
                Map<String, PictureData> detailPictrues = new LinkedHashMap<>();

                // 读取左上端单元格
                Row row = sheet.getRow(i);
                // 行不为空
                if (row != null) {
                    // 获取到Excel文件中的所有的列
                    int cells = row.getLastCellNum();

                    List<String> asList = new LinkedList<>();
                    // 遍历列
                    for (int j = 0; j < cells; j++) {
                        // 获取到列的值
                        Cell cell = row.getCell(j);
                        String cellValue = getCellValue(cell);
                        asList.add(cellValue);
                        if (count == 0) {
                            if ("详情".equals(cell.getStringCellValue())) {
                                isHasDetail = true;
                            }
                        }
                        String picIndexKey = String.valueOf(i) + "_" + String.valueOf(j);
                        List<String> picIndexList = picIndexMap.get(picIndexKey);
                        if (picIndexList != null && sheetIndexPicMap != null) {
                            //获取图片
                            if (j == 4) {
                                for (String picIndex : picIndexList) {
                                    sheetPictrues.put(picIndex, sheetIndexPicMap.get(picIndex));
                                }
                            }
                            //获取详情图片
                            if (isHasDetail && j == 5) {
                                for (String picIndex : picIndexList) {
                                    detailPictrues.put(picIndex, sheetIndexPicMap.get(picIndex));
                                }
                            }
                        }
                    }

                    //首先判断所需表头是否存在 如果不存在 提示错误
                    if (count == 0) {
                        for (int j = 0; j < skuNames.size(); j++) {
                            if (!asList.contains(skuNames.get(j))) {
                                return new Result<List<SkuProductVO>>(SkuResultCode.DATA_ERROR, "表头" + skuNames.get(j) + "不存在");
                            } else {
                                skuLocation.put(j + (asList.size() - skuNames.size()), skuNames.get(j));
                            }
                        }
                    } else {
                        if (asList.size() > 0) {
                            //根据位置获取
                            SkuProductVO skuProductVO = new SkuProductVO();
                            skuProductVO.setProductName(asList.get(0));
                            skuProductVO.setModel(asList.get(1));
                            skuProductVO.setPrice(StringUtils.isNotBlank(asList.get(2)) ? new BigDecimal(asList.get(2)) : null);
                            skuProductVO.setAddress(asList.get(3));
                            skuProductVO.setSalesMaintenance(asList.get(6));
                            //获取详情文字内容
                            if (isHasDetail && asList.size() > 5 && StringUtils.isNotBlank(asList.get(5))) {
                                skuProductVO.setDescription(asList.get(5));
                            }
                            Map<String, SkuItemAttributeVO> skus = new HashMap<>();
                            for (Integer key : skuLocation.keySet()) {
                                String title = skuLocation.get(key);
                                if (key < asList.size()) {
                                    String skuName = asList.get(key);

                                    SkuItemAttributeVO skuItemAttributeVO = new SkuItemAttributeVO();
                                    skuItemAttributeVO.setValue(skuName);
                                    skus.put(title, skuItemAttributeVO);
                                    skuProductVO.setSkus(skus);
                                }
                            }
                            skuProductVOs.add(skuProductVO);

                            skuProductVO.setSheetPictrues(sheetPictrues);
                            skuProductVO.setDetailPictrues(detailPictrues);
                        }
                    }

                    count++;
                }
            }
        } catch (Exception e) {
            LOGGER.error("readExcelUtil exception{}", e);
            return new Result<List<SkuProductVO>>(SkuResultCode.DATA_ERROR);
        }

        return new Result<List<SkuProductVO>>(skuProductVOs);
    }

    /**
     * 获取excel单元格内容
     *
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell) {
        String cellValue = "";
        if (cell != null) {
            if (HSSFCell.CELL_TYPE_STRING != cell.getCellType()) {
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
            }
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_FORMULA:
                    break;
                case HSSFCell.CELL_TYPE_NUMERIC:
                    cellValue = String.valueOf(cell.getNumericCellValue());
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    cellValue = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BLANK:
                    break;
                case HSSFCell.CELL_TYPE_ERROR:
                    break;
                default:
                    break;
            }
        }
        return cellValue;
    }

    private static Map<String, PictureData> getSheetPictrues03(Map<String, List<String>> picIndexMap,
            HSSFSheet sheet, HSSFWorkbook workbook) {

        Map<String, PictureData> sheetIndexPicMap = new LinkedHashMap<String, PictureData>();
        List<HSSFPictureData> pictures = workbook.getAllPictures();

        List<String> picIndexList;
        if (pictures.size() != 0) {
            for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
                HSSFClientAnchor anchor = (HSSFClientAnchor) shape.getAnchor();
                int row1 = anchor.getRow1();
                int col1 = anchor.getCol1();
                if (shape instanceof HSSFPicture) {
                    HSSFPicture pic = (HSSFPicture) shape;
                    int pictureIndex = pic.getPictureIndex() - 1;
                    HSSFPictureData picData = pictures.get(pictureIndex);
                    String picIndex = UUID.randomUUID().toString();
                    String picIndexKey = String.valueOf(row1) + "_" + String.valueOf(col1);
                    if (picIndexMap.containsKey(picIndexKey)) {
                        picIndexList = picIndexMap.get(picIndexKey);
                    } else {
                        picIndexList = new LinkedList<>();
                    }
                    picIndexList.add(picIndex);
                    picIndexMap.put(picIndexKey, picIndexList);
                    sheetIndexPicMap.put(picIndex, picData);
                }
            }
            return sheetIndexPicMap;
        }
        return null;
    }

    private static Map<String, PictureData> getSheetPictrues07(Map<String, List<String>> picIndexMap,
            XSSFSheet sheet, XSSFWorkbook workbook) {
        Map<String, PictureData> sheetIndexPicMap = new LinkedHashMap<String, PictureData>();

        List<String> picIndexList;
        for (POIXMLDocumentPart dr : sheet.getRelations()) {
            if (dr instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) dr;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture pic = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = pic.getPreferredSize();
                    CTMarker ctMarker = anchor.getFrom();
                    String picIndexKey = ctMarker.getRow() + "_" + ctMarker.getCol();
                    String picIndex = picIndexKey + "_" + String.valueOf(picIndexMap.get(picIndexKey).size() + 1);
                    if (picIndexMap.containsKey(picIndexKey)) {
                        picIndexList = picIndexMap.get(picIndex);
                    } else {
                        picIndexList = new LinkedList<>();
                    }
                    picIndexList.add(picIndex);
                    picIndexMap.put(picIndexKey, picIndexList);
                    sheetIndexPicMap.put(picIndex, pic.getPictureData());
                }
            }
        }

        return sheetIndexPicMap;
    }  

    public static boolean isNumber(String str) {// 判断小数
        return str.matches("\\d+\\.\\d+$");
    }
}
